/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.comut.crud.dao;

import com.comut.crud.pojo.FindAndSet;
import com.comut.crud.pojo.group.Group;
import com.comut.lib.error.ErrorCodes;
import com.comut.lib.exception.BaseException;
import com.comut.lib.exception.ObjectFoundException;
import com.comut.lib.exception.ObjectNotFoundException;
import java.sql.*;
import org.apache.log4j.Logger;

/**
 *
 * @author asantos
 */
public class GroupDao {

    private static Logger _logger = Logger.getLogger(GroupDao.class.getName());
    private Connection _connection;

    public GroupDao(Connection connection) {
        this._connection = connection;
    }

    /**
     * get the group info
     * @param id
     * @return 
     */
    public ResultSet getGroup(int id) {

        ResultSet rs = null;
        CallableStatement stmt = null;
        String query = "{call [crud].[usp_getGroup](?)}";
        try {
            stmt = _connection.prepareCall(query);
            stmt.setInt(1, id);
            rs = stmt.executeQuery();
        } catch (SQLException ex) {
            _logger.error("getGroup error");
            throw new BaseException(ex, ex.getErrorCode());
        } finally {
        }

        return rs;
    }

    /**
     * create group
     * @param group
     * @return 
     */
    public int createGroup(Group group) {

        int toRet = -1;
        ResultSet rs = null;
        CallableStatement stmt = null;
        String query = "{call [crud].[usp_createGroup](?,?,?,?,?)}";
        try {

            stmt = _connection.prepareCall(query);
            stmt.setInt(1, group.getId_channel());
            stmt.setString(2, group.getAlias());
            stmt.setInt(3, group.getId_owner());
            stmt.setInt(4, group.getId_logo());
            stmt.setString(5, group.getPoi_category_description());            
            rs = stmt.executeQuery();

            if (rs.next()) {
                toRet = rs.getInt(1);
            }
        } catch (SQLException ex) {
            _logger.error("createGroup error", ex);
            if (ex.getErrorCode() == ErrorCodes.OBJECT_FOUND) {
                throw new ObjectFoundException(ex, ex.getErrorCode());
            } else {
                throw new BaseException(ex, ex.getErrorCode());
            }
        } catch (Exception ex) {
            _logger.error("createGroup error", ex);
            throw new BaseException(ex, ErrorCodes.GENERAL_ERROR);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ignore) {
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ignore) {
                }
            }
        }

        return toRet;
    }

    /**
     * Update the group table in SQLServer
     *
     * @param group Object with the where clause and the update clause
     */
    public void updateGroup(FindAndSet group) {

        if(group.getUpdate().isEmpty() || group.getFind().isEmpty()){
            _logger.info("The table of group will not be updated.");
        }
        else{
            String update = "UPDATE [auth].[struct_group] SET "+group.getUpdate();
            String where = " WHERE "+group.getFind();

            PreparedStatement stmt = null;
            try {
                stmt = _connection.prepareStatement(update + where);
                int updateRows = stmt.executeUpdate();
                if (updateRows == 0) {
                    throw new ObjectNotFoundException("The group does not exist", ErrorCodes.OBJECT_NOT_FOUND);
                }
            } catch (SQLException ex) {
                _logger.fatal("updateGroup error", ex);
                throw new BaseException(ex, ex.getErrorCode());
            } finally {
                if (stmt != null) {
                    try {
                        stmt.close();
                    } catch (SQLException ignore) {
                    }
                }
            }
        }
        
    }

    /**
     * delete the group and the association in POI and Tags tables
     * @param id 
     */
    public void deleteGroup(int id) {
        CallableStatement stmt = null;
        String query = "{call [crud].[usp_deleteGroup](?)}";
        try {
            stmt = _connection.prepareCall(query);
            stmt.setInt(1, id);
            stmt.execute();
        } catch (SQLException ex) {
            _logger.error("deleteGroup error", ex);
            if (ex.getErrorCode() == ErrorCodes.OBJECT_NOT_FOUND) {
                throw new ObjectFoundException(ex, ex.getErrorCode());
            } else {
                throw new BaseException(ex, ex.getErrorCode());
            }
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ignore) {
                }
            }
        }
    }
    
    /**
     * delete the association between tags and groups
     * @param id 
     */
    public void deleteGroupTags(int id) {
        CallableStatement stmt = null;
        String query = "{call [crud].[usp_deleteGroupTags](?)}";
        try {
            stmt = _connection.prepareCall(query);
            stmt.setInt(1, id);
            stmt.execute();
        } catch (SQLException ex) {
            _logger.error("deleteGroupTags error", ex);
            if (ex.getErrorCode() == ErrorCodes.OBJECT_NOT_FOUND) {
                throw new ObjectFoundException(ex, ex.getErrorCode());
            } else {
                throw new BaseException(ex, ex.getErrorCode());
            }
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ignore) {
                }
            }
        }
    }
    
    
    /**
     * insert the association between tags and groups
     * @param id 
     */
    public void insertGroupTags(int id_group, int id_tag) {
        CallableStatement stmt = null;
        String query = "{call [crud].[usp_createGroupTag](?,?)}";
        try {
            stmt = _connection.prepareCall(query);
            stmt.setInt(1, id_group);
            stmt.setInt(2, id_tag);
            stmt.execute();
        } catch (SQLException ex) {
            _logger.error("insertGroupTags error", ex);
            if (ex.getErrorCode() == ErrorCodes.OBJECT_NOT_FOUND) {
                throw new ObjectFoundException(ex, ex.getErrorCode());
            } else {
                throw new BaseException(ex, ex.getErrorCode());
            }
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ignore) {
                }
            }
        }
    }
    
    /**
     * get the tag names associated with the group
     * @param id_group
     * @return 
     */
    public ResultSet getTagsNames(int id_group) {

        ResultSet rs = null;
        CallableStatement stmt = null;
        String query = "{call [crud].[usp_tagsOfGroup](?)}";
        try {
            stmt = _connection.prepareCall(query);
            stmt.setInt(1, id_group);
            rs = stmt.executeQuery();
        } catch (SQLException ex) {
            _logger.error("getTagsNames error");
            throw new BaseException(ex, ex.getErrorCode());
        } finally {
        }

        return rs;
    }
    
    
    /**
     * get the ResulSet with the user emails associated with the group id_group
     * @param id_group
     * @return 
     */
    public ResultSet getUsersFromGroup(int id_group) {

        ResultSet rs = null;
        CallableStatement stmt = null;
        String query = "{call [crud].[usp_usersOfGroup](?)}";
        try {
            stmt = _connection.prepareCall(query);
            stmt.setInt(1, id_group);
            rs = stmt.executeQuery();
        } catch (SQLException ex) {
            _logger.error("getUserEmailsFromGroup error");
            throw new BaseException(ex, ex.getErrorCode());
        } finally {
        }

        return rs;
    }
    
    /**
     * get the groups
     * @return 
     */
    public ResultSet getGroups() {

        ResultSet rs = null;
        CallableStatement stmt = null;
        String query = "{call [crud].[usp_getGroups]}";
        try {
            stmt = _connection.prepareCall(query);
            rs = stmt.executeQuery();
        } catch (SQLException ex) {
            _logger.error("getGroups error");
            throw new BaseException(ex, ex.getErrorCode());
        } finally {
        }

        return rs;
    }
    
}
